﻿using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Globalization;
using System.Linq;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;
using XQ.WeChatOfficialAccount.DAL.Model;
using System.Data.SqlClient;
using XQ.Framework.core;

namespace XQ.WeChatOfficialAccount.DAL.DAL
{
    public class UserDal : BaseDal<Sys_Users>
    {
        DepartmentDal departmentDal = new DepartmentDal();

        public UserDal()
        {
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns></returns>
        public List<UserExModel> GetUserList(int pageSize, int index, string userName, string trueName, int deptID, Pagination page)
        {

            List<UserExModel> list = new List<UserExModel>();
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                List<SqlParameter> sqlPageParams = new List<SqlParameter>();
                StringBuilder pageSql = new StringBuilder();
                pageSql.Append("select 1 ");
                pageSql.Append(" from Sys_Users u inner join Sys_Departments d on u.DepartmentID=d.Id");
                pageSql.Append(" where (u.isdel is null or u.isdel =0 or u.isdel=1) ");
                if (!string.IsNullOrEmpty(userName))
                {
                    pageSql.Append(" and u.username like '%' + @username + '%'");
                    sqlPageParams.Add(new SqlParameter("@username", userName));
                }
                if (!string.IsNullOrEmpty(trueName))
                {
                    pageSql.Append(" and u.trueName like '%' + @trueName + '%'");
                    sqlPageParams.Add(new SqlParameter("@trueName", trueName));
                }
                if (deptID > 1)
                {
                    List<int> deptIds = departmentDal.GetAllChildDeptIDs(deptID);
                    deptIds.Add(deptID);
                    if (deptIds.Count > 0)
                    {
                        pageSql.Append(" and u.DepartmentID in(" + string.Join(",", deptIds.ToArray()) + ")");
                    }
                }
                int allCount = ce.Database.SqlQuery<int>(pageSql.ToString(), sqlPageParams.ToArray()).Count();
                page.RecordCount = allCount;
                page.PageCount = (int)Math.Ceiling((decimal)page.RecordCount / (decimal)pageSize);
                page.PageSize = pageSize;
                page.PageIndex = index;

                index = (index - 1) * pageSize;
                List<SqlParameter> sqlParams = new List<SqlParameter>
                {
                    new SqlParameter("@pageSize", pageSize),
                    new SqlParameter("@begin", index)
                };
                StringBuilder sql = new StringBuilder();
                sql.Append("select top (@pageSize) t.*,(select rolename from sys_roles where id=t.roleid) as roleName from");
                sql.Append("(select ROW_NUMBER() over(order by u.IsDisabled, u.id desc) num, u.*,d.DepartmentName, ");
                sql.Append(" (select top 1 RoleID from Sys_User_Roles where userid=u.id) as RoleID");
                sql.Append(" from Sys_Users u inner join Sys_Departments d on u.DepartmentID=d.Id");
                sql.Append(" where (u.isdel is null or u.isdel =0 or u.isdel=1) ");
                if (!string.IsNullOrEmpty(userName))
                {
                    sql.Append(" and u.username like '%' + @username + '%'");
                    sqlParams.Add(new SqlParameter("@username", userName));
                }
                if (!string.IsNullOrEmpty(trueName))
                {
                    sql.Append(" and u.trueName like '%' + @trueName + '%'");
                    sqlParams.Add(new SqlParameter("@trueName", trueName));
                }
                if (deptID > 1)
                {
                    List<int> deptIds = departmentDal.GetAllChildDeptIDs(deptID);
                    deptIds.Add(deptID);
                    if (deptIds.Count > 0)
                    {
                        sql.Append(" and u.DepartmentID in(" + string.Join(",", deptIds.ToArray()) + ")");
                    }
                }
                sql.Append(")t");
                sql.Append(" where t.num>@begin");

                list = ce.Database.SqlQuery<UserExModel>(sql.ToString(), sqlParams.ToArray()).ToList();
            }

            return list;
        }

        /// <summary>
        /// 根据ID数据获取用户列表
        /// </summary>
        /// <returns></returns>
        public List<Sys_Users> GetUsersByIds(List<int> userIds)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                return ce.Sys_Users.Where(u => userIds.Contains(u.id)).ToList();
            }
        }

        /// <summary>
        /// 根据部门和角色获取用户列表
        /// </summary>
        /// <param name="departmentId"></param>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public List<UserExModel> GetUserList(int departmentId, int roleId)
        {
            List<UserExModel> list = new List<UserExModel>();
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                StringBuilder sql = new StringBuilder();
                sql.Append("select u.*,r.RoleName,d.DepartmentName from Sys_Users u ");
                sql.Append("inner join Sys_User_Roles ur on u.id = ur.UserID ");
                sql.Append("inner join Sys_Roles r on ur.RoleID = r.ID ");
                sql.Append("inner join Sys_Departments d on u.DepartmentID = d.Id ");
                sql.Append(" where (u.isdel is null or u.isdel =0) and isdisabled=0 ");
                if (departmentId != 0)
                {
                    sql.Append(" and d.id=@departmentId");
                    sqlParams.Add(new SqlParameter("@departmentId", departmentId));
                }
                if (roleId != 0)
                {
                    sql.Append(" and r.id=@roleId");
                    sqlParams.Add(new SqlParameter("@roleId", roleId));
                }

                list = ce.Database.SqlQuery<UserExModel>(sql.ToString(), sqlParams.ToArray()).ToList();
            }

            return list;
        }

        /// <summary>
        /// 获取指定条件的用户数量
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="trueName"></param>
        /// <returns></returns>
        public string GetUsersNum(string userName, string trueName)
        {
            int n;
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string sql = @"select count(*) from Sys_Users u inner join Sys_Departments d on u.DepartmentID=d.Id where (u.isdel is null or u.isdel =0)";
                List<SqlParameter> sqlParams = new List<SqlParameter>();

                if (!string.IsNullOrEmpty(userName))
                {
                    sql += " and u.username like '%' + @username + '%'";
                    sqlParams.Add(new SqlParameter("@username", userName));

                }
                if (!string.IsNullOrEmpty(trueName))
                {
                    sql += " and u.truename like '%' + @truename + '%'";
                    sqlParams.Add(new SqlParameter("@truename", trueName));
                }

                n = ce.Database.SqlQuery<int>(sql, sqlParams.ToArray()).First();
            }

            return n.ToString(CultureInfo.InvariantCulture);
        }

        /// <summary>
        /// 保存用户
        /// </summary>
        /// <param name="userModel"></param>
        /// <returns></returns>
        public int SaveUser(Sys_Users userModel)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                //验证是否存在相同的用户名
                int num = ce.Sys_Users.Count(u => u.UserName == userModel.UserName);
                if (num == 0)
                {
                    userModel.Password = SecureHelper.getMD5(userModel.Password);
                    ce.Sys_Users.Add(userModel);
                    if (ce.SaveChanges() == 1)
                    {
                        return userModel.id;
                    }
                }
                else
                {
                    return 999999999;
                }
            }
            return 0;
        }

        /// <summary>
        /// 修改用户
        /// </summary>
        /// <param name="userModel"></param>
        /// <returns></returns>
        public int UpdateUser(Sys_Users userModel)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                //验证是否存在相同的用户名
                int num = ce.Sys_Users.Count(u => u.UserName == userModel.UserName && u.id != userModel.id);
                if (num > 0)
                {
                    return 999999999;
                }
                Sys_Users oldUserModel = ce.Sys_Users.First(u => u.id == userModel.id);
                oldUserModel.IsDisabled = userModel.IsDisabled;
                //用户名不能修改
                //oldUserModel.UserName = userModel.UserName;
                oldUserModel.TrueName = userModel.TrueName;
                if (oldUserModel.Password != userModel.Password)
                {
                    oldUserModel.Password = SecureHelper.getMD5(userModel.Password);
                }
                oldUserModel.DepartmentID = userModel.DepartmentID;
                oldUserModel.Remark = userModel.Remark;
                oldUserModel.ModTime = userModel.ModTime;
                oldUserModel.ModUserID = userModel.ModUserID;
                oldUserModel.Phone = userModel.Phone;
                if (ce.SaveChanges() == 1)
                {
                    return oldUserModel.id;
                }
            }
            return 0;
        }

        /// <summary>
        /// 根据ID获取用户
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public Sys_Users GetUserByID(int userid)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string sql = "select u.*,(select roleid from Sys_User_Roles where userid=u.id) as roleid ,d.departmentname from sys_users u inner join sys_departments d on u.departmentid=d.id where u.id=@id";
                object[] par = { 
                            new SqlParameter("@id",userid),
                            };
                return ce.Database.SqlQuery<UserExModel>(sql, par).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据openid获取用户
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public UserExModel GetUserByOpenid(string openid)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string sql = @" select u.*,
                                ur.roleID as roleid ,
                                (select roleName from Sys_Roles where id = ur.roleid) as roleName,
                                d.departmentname from sys_users u
                                inner join sys_departments d on u.departmentid = d.id
                                inner join Sys_User_Roles ur on ur.userid = u.id
                                where u.openid = @openid";
                object[] par = {
                            new SqlParameter("@openid",openid),
                            };
                return ce.Database.SqlQuery<UserExModel>(sql, par).FirstOrDefault();
            }
        }

        /// <summary>
        /// 根据ID删除用户
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public bool DeleteUser(int userid)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                //真删
                ce.Sys_Users.Remove(ce.Sys_Users.First(u => u.id == userid));
                if (ce.SaveChanges() == 1)
                {
                    return true;
                }

                ////标识删除
                //Sys_Users oldUserModel = ce.Sys_Users.First(u => u.id == userid);
                //oldUserModel.IsDel = true;
                //if (ce.SaveChanges() == 1)
                //{
                //    return true;
                //}
            }
            return false;
        }

        /// <summary>
        /// 删除用户(多删除)
        /// </summary>
        /// <param name="userIds"></param>
        /// <returns></returns>
        public bool DeleteUsers(List<int> userIds)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                if (userIds != null && userIds.Count > 0)
                {
                    foreach (int userid in userIds)
                    {
                        Sys_Users user = ce.Sys_Users.FirstOrDefault(u => u.id == userid);
                        if (user != null)
                        {
                            ce.Sys_Users.Remove(user);
                        }
                    }
                }
                return ce.SaveChanges() > 0;
            }
        }

        /// <summary>
        /// 根据用户ID获取对应权限ID
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetRoleByUserID(int userid)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                int? roleIdTemp = 0;
                Sys_User_Roles urModel = ce.Sys_User_Roles.FirstOrDefault(ur => ur.UserID == userid);
                if (urModel != null)
                {
                    roleIdTemp = urModel.RoleID;
                }
                int roleId = roleIdTemp == null ? 0 : (int)roleIdTemp;
                return roleId;
            }
        }

        /// <summary>
        /// 保存用户对应的权限
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public bool SaveUserRole(int userid, int roleid)
        {
            try
            {
                using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
                {
                    Sys_User_Roles urModel = ce.Sys_User_Roles.FirstOrDefault(ur => ur.UserID == userid);
                    if (urModel == null)
                    {
                        urModel = new Sys_User_Roles();
                        urModel.UserID = userid;
                        urModel.RoleID = roleid;
                        ce.Sys_User_Roles.Add(urModel);
                        if (ce.SaveChanges() == 1)
                        {
                            return true;
                        }
                    }
                    else
                    {
                        urModel.RoleID = roleid;
                        if (ce.SaveChanges() == 1)
                        {
                            return true;
                        }
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            
        }


        /// <summary>
        /// 更改密码
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="newPwd"></param>
        /// <returns></returns>
        public bool ChangePwd(int userId, string newPwd)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string pwdMd5 = SecureHelper.getMD5(newPwd);
                return ce.Database.ExecuteSqlCommand("update Sys_Users set Password=@password where id=@userId",
                       new object[]
                    {
                       new SqlParameter("@password",pwdMd5), 
                       new SqlParameter("@userId",userId) 
                    }) > 0;
            }
        }
        /// <summary>
        /// 检查密码是否正确
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool CheckPwdIsRight(int userId, string pwd)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string pwdMd5 = SecureHelper.getMD5(pwd);
                return
                    ce.Sys_Users.Count(n => n.id == userId && n.Password == pwdMd5) > 0;
            }
        }

        /// <summary>
        /// 按部门获取列表
        /// </summary>
        /// <param name="depId">部门列表</param>
        /// <returns>部门列表</returns>
        public List<Sys_Users> GetUsersByDep(int depId)
        {
            using (WeChatOfficialAccountEntities ce=new WeChatOfficialAccountEntities())
            {
               return ce.Sys_Users.Where(n => n.DepartmentID == depId).ToList();
            }
        }

       
        /// <summary>
        /// 返回考核员工
        /// </summary>
        /// <returns></returns>
        public List<Sys_Users> GetPerUsers()
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
               return ce.Database.SqlQuery<Sys_Users>("select * from Sys_Users u join PerFormanceUser p on u.id=p.userID ").ToList();
            }
        }

        //获取指定部门的人员
        public List<Sys_Users> GetUsersByDeptID(int deptId)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                return ce.Sys_Users.Where(n => n.DepartmentID == deptId).ToList();
            }
        }

        /// <summary>
        /// 根据用户ID获取部门ID
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int? GetDeptIDByUserID(int userId)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                var user = ce.Sys_Users.FirstOrDefault(u => u.id == userId);
                if (user != null)
                {
                    return user.DepartmentID;
                }
                return 0;
            }
        }
    }
}
